package edu.cmu.aes.project2.wshelper;

/**
 This file: XMLProcessing.java
 Programmer: Prasanna Vaidyanathan (pvaidyan@andrew.cmu.edu)
 Course/Section: Architecting Enterprise Systems F11 A
 Assignment: Project 1
 Description: This file provides the capabilities to search models, view reviews, add review and sort 
 the data in XML file.
 Last Modified: 10/02/11
 */

import java.io.File;
import java.io.IOException;
import java.sql.Connection;
import java.sql.Date;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Calendar;
import java.util.Scanner;
import java.util.StringTokenizer;

import javax.xml.transform.Result;
import javax.xml.transform.Source;
import javax.xml.transform.Transformer;
import javax.xml.transform.TransformerConfigurationException;
import javax.xml.transform.TransformerException;
import javax.xml.transform.TransformerFactory;
import javax.xml.transform.dom.DOMSource;
import javax.xml.transform.stream.StreamResult;
import javax.xml.xquery.*;
import javax.xml.xquery.XQDataSource.*;

import javax.xml.parsers.*;
import javax.xml.xpath.*;

import net.sf.saxon.xqj.SaxonXQDataSource;

//import org.hsqldb.lib.Iterator;
import org.w3c.dom.*;
import org.xml.sax.SAXException;

import edu.cmu.aes.share.MySqlProperties;

/**
 * Implements functions for processing, manipulating and presenting XML data to the user
 * @author Prasanna
 *
 */
public class XMLProcessing {
	
	private static String databaseLocation = MySqlProperties.getURL()+"/aes_project2";
	private static String userName = MySqlProperties.getUserName();
	private static String password = MySqlProperties.getPassword();
	
	private static String fileLocation = "D:/AllWorkSpace/workspace-aes/Supplier/Input/consolidated_data.xml";
	//private static String fileLocation = "Input/consolidated_data.xml";

	private static Connection getConnection() throws SQLException {
		Connection connection = null;

		try {
			Class.forName("com.mysql.jdbc.Driver").newInstance();
			connection = DriverManager.getConnection(databaseLocation,
					userName, password);
		} catch (Exception e) {
			throw new SQLException("Cannot instantiate database connector");
		}
		return connection;
	}
	

	/**
	 * @param args
	 * @throws XPathExpressionException
	 * @throws IOException
	 * @throws SAXException
	 * @throws ParserConfigurationException
	 * @throws ClassNotFoundException
	 * @throws IllegalAccessException
	 * @throws InstantiationException
	 * @throws XQException
	 * @throws TransformerException
	 * @throws SQLException 
	 */
	public static void main(String[] args) throws XPathExpressionException,
			SAXException, IOException, ParserConfigurationException,
			InstantiationException, IllegalAccessException,
			ClassNotFoundException, XQException, TransformerException, SQLException {
		// TODO Auto-generated method stub
//
////		ArrayList<String> result;
		XMLProcessing xml = new XMLProcessing();
//		BikeInfo bikeinfo;
//		bikeinfo = xml.getDetails("Bianchi MONO-Q Ultegra Compact 2010 Road Bike");
//		//System.out.println(bikeinfo);
//		
//	
//////		for(String i : result){ 
//////			System.out.println(i);
//////		}
////		
		Calendar cal = Calendar.getInstance();
		OrderItems[] orderitems = new OrderItems[2];
		orderitems[0] = new OrderItems("Bianchi","Bianchi 2010",5);
		orderitems[1] = new OrderItems("Pegasus","Pegasus 2010",10);
		java.sql.Date date = new java.sql.Date(cal.getTime().getTime());
		Order order = new Order(orderitems,"Prasanna",date,500);
		

		xml.placeOrderRollback(order);
		

	}

	/**
	 * This function lists all the available brands and asks the user to choose
	 * a brand to continue
	 * 
	 * @param xqutil
	 *            contains the necessary class objects which will be used by the
	 *            XQuery API to manipulate data in XML
	 * @throws XQException
	 * @throws XPathExpressionException
	 * @throws ParserConfigurationException
	 * @throws SAXException
	 * @throws IOException
	 * @throws TransformerException
	 */
	public ArrayList<String> getBrands() 
			 {
		
		XQueryUtils xqutil = null;
		try {
			xqutil = new XQueryUtils();
		} catch (XQException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}

		ArrayList<String> brands = new ArrayList<String>();
		String es = "let $list := doc('"+fileLocation+"')/models/model/manufacturer "
				+ "for $n in  distinct-values($list) "
				+ "return <manufacturer>{$n}</manufacturer>";
		// execute the XQuery expression
		try {
			xqutil.setResult(xqutil.getExpr().executeQuery(es));
		} catch (XQException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		// process the result (sequence) iteratively

		Node node;
		NodeList nodeDetails = null;

		try {
			while (xqutil.getResult().next()) {
				node = xqutil.getResult().getNode();
				nodeDetails = node.getChildNodes();
				brands.add(nodeDetails.item(0).getNodeValue());
				
			}
		} catch (DOMException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (XQException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}

		return brands;
	}

	
	/**
	 * This function displays the List of available models in the brand chosen
	 * by the user
	 * 
	 * @param xqutil
	 * @param brand
	 * @throws XQException
	 * @throws XPathExpressionException
	 * @throws ParserConfigurationException
	 * @throws SAXException
	 * @throws IOException
	 * @throws TransformerException
	 */
	public ArrayList<String> getModels( String brand) {

		XQueryUtils xqutil = null;
		try {
			xqutil = new XQueryUtils();
		} catch (XQException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		ArrayList<String> models = new ArrayList<String>();

		String es = "for $n in fn:doc('"+fileLocation+"')/models/model "
				+ "where $n/manufacturer ='"
				+ brand
				+ "' "
				+ "return fn:data($n/@name)";
		// execute the XQuery expression
		try {
			xqutil.setResult(xqutil.getExpr().executeQuery(es));
		} catch (XQException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		// process the result (sequence) iteratively
		try {
			while (xqutil.getResult().next()) {
				// retrieve the current item of the sequence as a String
				String str = xqutil.getResult().getAtomicValue();
				models.add(str);
			}
		} catch (XQException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return models;

	}

	
	/**
	 * This function prints all the details of the model such as price,
	 * manufacturer, raating etc
	 * 
	 * @param xqutil
	 * @param model
	 * @throws XQException
	 */
	public BikeInfo getDetails(String model) {

		XQueryUtils xqutil = null;
		try {
			xqutil = new XQueryUtils();
		} catch (XQException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		String es = "for $n in doc('"+fileLocation+"')/models/model "
				+ "where $n/@name ='" + model + "' " + "return $n/*";
		// execute the XQuery expression
		try {
			xqutil.setResult(xqutil.getExpr().executeQuery(es));
		} catch (XQException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		// process the result (sequence) iteratively

		BikeInfo bikeinfo = new BikeInfo();
		boolean isFound = false;
		Node node;
		NodeList nodeDetails = null;
		try {
			while (xqutil.getResult().next()) {
				isFound = true;
				
				node = xqutil.getResult().getNode();

				nodeDetails = node.getChildNodes();
				int length = nodeDetails.getLength();
				String parentNodeName = node.getNodeName();
				String content = "";
				
				
			
				if (length > 1) {
					nodeDetails = node.getChildNodes();
					for (int i = 0; i < nodeDetails.getLength(); i++) {
						
			            if(nodeDetails.item(i).getNodeName().equals("element")){
			            	
			            	NamedNodeMap attributeslist = nodeDetails.item(i).getAttributes();
			            	Attr attribute = (Attr)attributeslist.item(1);
			            	
			            	content+= attribute.getNodeValue() + ":" + nodeDetails.item(i).getTextContent()+"\n";	
			            }
			            else
			            {	
			            content += nodeDetails.item(i).getNodeName() + ":" + nodeDetails.item(i).getTextContent() + "\n";	
			            }
					}
				} else
				{
					content += node.getNodeName() + ":" + node.getTextContent() + "\n";
				}
				
				
				if(parentNodeName.equalsIgnoreCase("manufacturer")){
					bikeinfo.setManufacturer(content.substring(13, content.length()));
				}
				else if(parentNodeName.equalsIgnoreCase("price")){
					try{
						bikeinfo.setPrice((int)Double.parseDouble(content.substring(6, content.length())));
					}catch(Exception e){
						bikeinfo.setPrice(-1);
					}
				}
				
				else if(parentNodeName.equalsIgnoreCase("year")){
					bikeinfo.setYear(content.substring(5, content.length()));
				}
				
				else if(parentNodeName.equalsIgnoreCase("frameset")){
					
					//System.out.println("content"+content);
					ArrayList<DataElement> dataelementarray = new ArrayList<DataElement>();
					String[] line = content.split("\n");
					for(int j=0;j<line.length;j++){
						//System.out.println(line[j]);
					String[] st = line[j].split(":");
					DataElement dataelement = new DataElement();
						dataelement.setName(st[0]);
						dataelement.setValue(st[1]);
						//dataelement.setValue(st.nextToken());
						dataelementarray.add(dataelement);
					}
					
//					for(int i=0;i<dataelementarray.size();i++){
//						System.out.println("Name"+((DataElement) dataelementarray.get(i)).getName());
//						System.out.println("Value"+((DataElement) dataelementarray.get(i)).getValue());
//					}

					bikeinfo.setFrameset(dataelementarray);
				}
				else if(parentNodeName.equalsIgnoreCase("wheels")){
					ArrayList<DataElement> dataelementarray = new ArrayList<DataElement>();
					String[] line = content.split("\n");
					for(int j=0;j<line.length;j++){
						//System.out.println(line[j]);
					String[] st = line[j].split(":");
					DataElement dataelement = new DataElement();
						dataelement.setName(st[0]);
						dataelement.setValue(st[1]);
						//dataelement.setValue(st.nextToken());
						dataelementarray.add(dataelement);
					}
					
					bikeinfo.setWheels(dataelementarray);
				}
				else if(parentNodeName.equalsIgnoreCase("drivetrain")){
					
					ArrayList<DataElement> dataelementarray = new ArrayList<DataElement>();
					String[] line = content.split("\n");
					for(int j=0;j<line.length;j++){
						//System.out.println(line[j]);
					String[] st = line[j].split(":");
					DataElement dataelement = new DataElement();
						dataelement.setName(st[0]);
						dataelement.setValue(st[1]);
						//dataelement.setValue(st.nextToken());
						dataelementarray.add(dataelement);
					}
					bikeinfo.setDrivetrain(dataelementarray);
				}
				else if(parentNodeName.equalsIgnoreCase("rating")){
					
					ArrayList<DataElement> dataelementarray = new ArrayList<DataElement>();
					String[] line = content.split("\n");
					for(int j=0;j<line.length;j++){
					String[] st = line[j].split(":");
				
					if(j==0)
						bikeinfo.setRatingScore(st[1]);
					else
						bikeinfo.setRatingVote(st[1]);

					}
					
				}
				else if(parentNodeName.equalsIgnoreCase("components")){
					
					ArrayList<DataElement> dataelementarray = new ArrayList<DataElement>();
					String[] line = content.split("\n");
					for(int j=0;j<line.length;j++){
						//System.out.println(line[j]);
					String[] st = line[j].split(":");
					DataElement dataelement = new DataElement();
						dataelement.setName(st[0]);
						dataelement.setValue(st[1]);
						//dataelement.setValue(st.nextToken());
						dataelementarray.add(dataelement);
					}
					bikeinfo.setComponents(dataelementarray);
				}else if(parentNodeName.equalsIgnoreCase("review")){
					bikeinfo.setReview(content);
				}
				
				

			}
			//System.out.println(bikeinfo);
		} catch (DOMException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (XQException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		if(isFound)
			return bikeinfo;
		else 
			return null;

	}
	
	
	public boolean placeOrder(Order order) {
		
		
		Connection conn = null;
		try {
			conn = getConnection();
		} catch (SQLException e1) {
			// TODO Auto-generated catch block
			e1.printStackTrace();
		}
		OrderItems[] orderitems = order.getOrder();
		java.sql.Date sqlDate = new java.sql.Date(order.getDate().getTime());
			
			try{
			PreparedStatement pstmnt = conn.prepareStatement("insert into orders(customer_name,amount,date) values (?,?,?)");
			pstmnt.setString(1, order.getCustomerName());
			pstmnt.setInt(2, order.getAmount());
			pstmnt.setDate(3, sqlDate);
			int result = pstmnt.executeUpdate();
			
			
			pstmnt = conn.prepareStatement("select * from orders order by order_id desc limit 1");
			ResultSet resultset = pstmnt.executeQuery();
			
			int orderid = 0;
			if(resultset.next())
			orderid = Integer.parseInt(resultset.getString("order_id"));
			
			for(int i=0;i<orderitems.length;i++){	
		pstmnt = conn.prepareStatement("insert into orderitems(brand_name,model_name,quantity,order_id) values (?,?,?,?)");
		pstmnt.setString(1, orderitems[i].getBrandName());
		pstmnt.setString(2, orderitems[i].getModelName());
		pstmnt.setInt(3, orderitems[i].getQuantity());
		pstmnt.setInt(4,(orderid));
		result = pstmnt.executeUpdate();
		System.out.println(result);
			}
			}
			catch (Exception e){
				System.out.println(e);
				return false;
			}
		
		
		return true;
		
	}

public boolean placeOrderRollback(Order order) {
		
		
		Connection conn = null;
		try {
			conn = getConnection();
		} catch (SQLException e1) {
			// TODO Auto-generated catch block
			e1.printStackTrace();
		}
		OrderItems[] orderitems = order.getOrder();
		java.sql.Date sqlDate = new java.sql.Date(order.getDate().getTime());
			
			try{
			PreparedStatement pstmnt;
			
			
			pstmnt = conn.prepareStatement("select * from orders where customer_name = ? and date = ?");
			pstmnt.setString(1, order.getCustomerName());
			pstmnt.setDate(2, sqlDate);
			ResultSet resultset = pstmnt.executeQuery();
			
			
			int orderid = 0;
			if(resultset.next())
			orderid = Integer.parseInt(resultset.getString("order_id"));
			
			pstmnt = conn.prepareStatement("delete from orders where order_id = ?");
			pstmnt.setInt(1, orderid);
			int result = pstmnt.executeUpdate();
			
			
			
			
			for(int i=0;i<orderitems.length;i++){	
		pstmnt = conn.prepareStatement("delete from orderitems where order_id = ?");
		pstmnt.setInt(1, orderid);
		result = pstmnt.executeUpdate();
		System.out.println(result);
			}
			}
			catch (Exception e){
				System.out.println(e);
				return false;
			}
		
		
		return true;
		
	}



}


